﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace DomesticPestLib
{
   public class DLayer
    {

        SqlConnection conn;
        SqlCommand cmd;
        SqlDataReader reader;
        entyLoc enty = null;
        entyCommodity entyCom = null;
        entyInfo entyinfo = null;
        entyPest entypest = null;
        entyTrans entytrans = null;

        void Connect()
        {
            conn = new SqlConnection();
            conn.ConnectionString = "Data Source=KAJAL-PC;Initial Catalog=DomesticPestDB;Integrated Security=True";
            conn.Open();
            cmd = new SqlCommand();
            cmd.Connection = conn;
        }

        void Disconnect()
        {
            cmd.Dispose();
            conn.Dispose();
            conn.Close();
        }

        //Location (Country)
        public ArrayList selectLoc()
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Country";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                enty = new entyLoc();
                enty.Code = Convert.ToString(reader["Country_Code"]);
                enty.Name = Convert.ToString(reader["Country_Name"]);
                enty.GeoRegion = Convert.ToString(reader["Geographical Region"]);
                enty.State = Convert.ToString(reader["State_Name"]);
                enty.Location = Convert.ToString(reader["Location"]);
                enty.GIS = Convert.ToString(reader["GIS"]);

                alst.Add(enty);
            }
            Disconnect();
            return alst;
        }

        public ArrayList selectByCoutryCd(string cd)
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Country where Country_Code='" + cd + "'";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                enty = new entyLoc();
                //enty.Code = Convert.ToString(reader["Country_Code"]);
                enty.Name = Convert.ToString(reader["Country_Name"]);
                enty.GeoRegion = Convert.ToString(reader["Geographical Region"]);
                enty.State = Convert.ToString(reader["State_Name"]);
                enty.Location = Convert.ToString(reader["Location"]);
                enty.GIS = Convert.ToString(reader["GIS"]);

                alst.Add(enty);
            }
            Disconnect();
            return alst;
        }

        public bool UpdateLoc(entyLoc loc)
        {
            try
            {
                Connect();
                
                cmd.CommandText = "update Country set Country_Name='" + loc.Name + "',[Geographical Region]='" + loc.GeoRegion + "',State_Name='" + loc.State + "',Location='" + loc.Location + "',GIS='" + loc.GIS + "' where Country_Code='" + loc.Code + "'";

                cmd.ExecuteNonQuery();
                return true;
                
            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        public bool DeleteLoc(string loccd)
        {
            try
            {
                Connect();

                cmd.CommandText = "delete from Country where Country_Code='" + loccd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        //Commodity
        public ArrayList selectCommodity()
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Commodity";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                entyCom = new entyCommodity();
                entyCom.BCode = Convert.ToString(reader["BAYER_CODE"]);
                entyCom.Category = Convert.ToString(reader["CATEGORY"]);
                entyCom.CName = Convert.ToString(reader["CNAME"]);
                entyCom.SName = Convert.ToString(reader["SNAME"]);
                entyCom.Synonym = Convert.ToString(reader["SYNONYM"]);
                entyCom.SubSpcs = Convert.ToString(reader["SUBSPECIES"]);
                entyCom.FName = Convert.ToString(reader["FAMILY"]);
                entyCom.OName = Convert.ToString(reader["ORDER"]);
                alst.Add(entyCom);
            }
            Disconnect();
            return alst;
        }
       
        public bool SaveComm(entyCommodity entyCom)
        {
            try
            {
                Connect();

                cmd.CommandText = "insert into Commodity(BAYER_CODE,CATEGORY,CNAME,SNAME,SYNONYM,SUBSPECIES,FAMILY,[ORDER]) values ('" + entyCom.BCode  + "','" + entyCom.Category + "','" + entyCom.CName + "','" + entyCom.SName + "','" + entyCom.Synonym + "','" + entyCom.SubSpcs + "','" + entyCom.FName + "','" + entyCom.OName + "')";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();
        }

        public bool DeleteComm(string bcd)
        {
            try
            {
                Connect();

                cmd.CommandText = "delete from Commodity where BAYER_CODE='" + bcd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        public bool UpdateComm(entyCommodity com)
        {
            try
            {
                Connect();

                cmd.CommandText = "update Commodity set CATEGORY='" + com.Category + "',CNAME='" + com.CName + "',SNAME='" + com.SName + "',SYNONYM='" + com.Synonym + "',SUBSPECIES='" + com.SubSpcs + "',FAMILY='" + com.FName + "',[ORDER]='" + com.OName + "' where BAYER_CODE='" + com.BCode + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

       //Information

        public ArrayList selectInfo()
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from info";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                entyinfo = new entyInfo();
                entyinfo.InfoCd = Convert.ToString(reader["Info_Code"]);
                entyinfo.Author = Convert.ToString(reader["Author"]);
                entyinfo.Year = Convert.ToString(reader["year"]);
                entyinfo.Title  = Convert.ToString(reader["title"]);
                entyinfo.Journal= Convert.ToString(reader["journal"]);
                entyinfo.Vol = Convert.ToString(reader["volume"]);
                entyinfo.Pageref= Convert.ToString(reader["page_ref"]);
                entyinfo.Note = Convert.ToString(reader["notes"]);
                
                alst.Add(entyinfo);
            }
            Disconnect();
            return alst;
        }

        public bool SaveInfo(entyInfo entyInfo)
        {
            try
            {
                Connect();

                cmd.CommandText = "insert into info values ('" + entyInfo.InfoCd + "','" + entyInfo.Author + "','" + entyInfo.Year + "','" + entyInfo.Title + "','" + entyInfo.Journal + "','" + entyInfo.Vol + "','" + entyInfo.Pageref + "','" + entyInfo.Note + "')";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();
        }

        public bool DeleteInfo(string infocd)
        {
            try
            {
                Connect();

                cmd.CommandText = "delete from info where Info_Code='" + infocd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        public bool UpdateInfo(entyInfo info)
        {
            try
            {
                Connect();

                cmd.CommandText = "update info set Author='" + info.Author + "',year='" + info.Year + "',title='" + info.Title + "',journal='" + info.Journal + "',volume='" + info.Vol + "',page_ref='" + info.Pageref + "',notes='" + info.Note + "' where Info_Code='" + info.InfoCd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

       //Pest

        public ArrayList selectPest()
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Pest";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                entypest = new entyPest();
                entypest.PBCode= Convert.ToString(reader["BAYER_CODE"]);
                entypest.PSName = Convert.ToString(reader["SNAME"]);
                entypest.PCName = Convert.ToString(reader["CNAME"]);
                entypest.PKngdm = Convert.ToString(reader["KINGDOM"]);
                entypest.PPhylum = Convert.ToString(reader["PHYLUM"]);
                entypest.PSubPhylum = Convert.ToString(reader["SUBPHYLUM"]);
                entypest.PClass = Convert.ToString(reader["CLASS"]);
                entypest.POrder = Convert.ToString(reader["ORDER"]);
                entypest.PFName = Convert.ToString(reader["FAMILY"]);
                //entypest.PCategory = Convert.ToString(reader["CATEGORY"]);
                entypest.Painfo= Convert.ToString(reader["ainfo"]);
                entypest.PEcoImp = Convert.ToString(reader["ECOIMP"]);

                alst.Add(entypest);
            }
            Disconnect();
            return alst;
        }

        public bool SavePest(entyPest entypest)
        {
            try
            {
                Connect();

                cmd.CommandText = "insert into Pest values ('" + entypest.PBCode + "','" + entypest.PSName + "','" + entypest.PCName + "','" + entypest.PKngdm + "','" + entypest.PPhylum + "','" + entypest.PSubPhylum + "','" + entypest.PClass + "','" + entypest.POrder + "','" + entypest.PFName + "','" + entypest.PCategory + "','" + entypest.Painfo + "','" + entypest.PEcoImp + "')";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();
        }

        public bool DeletePest(string pestbcd)
        {
            try
            {
                Connect();

                cmd.CommandText = "delete from Pest where BAYER_CODE='" + pestbcd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        public bool UpdatePest(entyPest entypest)
        {
            try
            {
                Connect();

                cmd.CommandText = "update pest set SNAME='" + entypest.PSName + "',CNAME='" + entypest.PCName + "',KINGDOM='" + entypest.PKngdm + "',PHYLUM='" + entypest.PPhylum + "',SUBPHYLUM ='" + entypest.PSubPhylum + "',CLASS='" + entypest.PClass + "',[ORDER]='" + entypest.POrder + "',FAMILY='" + entypest.PFName + "',CATEGORY='" + entypest.PCategory + "',ainfo='" + entypest.Painfo + "',ECOIMP='" + entypest.PEcoImp + "' where BAYER_CODE='" + entypest.PBCode + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        } 

        //Transaction

        public bool SaveTrans(entyTrans entytran)
        {
            try
            {
                Connect();

                cmd.CommandText = "insert into Trans values ('" + entytran.TranCd + "','" + entytran.PestCd + "','" + entytran.HostCd + "','" + entytran.StateCd + "','" + entytran.TranInfo + "','" + entytran.TranPart + "')";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();
        }

        public ArrayList selectTrans()
        {
            ArrayList alst = null;
            Connect();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Trans";
            reader = cmd.ExecuteReader();
            alst = new ArrayList();

            while (reader.Read())
            {
                entytrans = new entyTrans();
                entytrans.TranCd = Convert.ToString(reader["TCode"]);
                entytrans.PestCd=Convert.ToString(reader["Pest_name"]);
                entytrans.HostCd=Convert.ToString(reader["Host_name"]);
                entytrans.StateCd=Convert.ToString(reader["State_name"]);
                entytrans.TranInfo=Convert.ToString(reader["Info"]);
                entytrans.TranPart = Convert.ToString(reader["PlantPart"]);
                
                alst.Add(entytrans);
            }
            Disconnect();
            return alst;
        }

        public bool DeleteTrans(string tcd)
        {
            try
            {
                Connect();

                cmd.CommandText = "delete * Trans where TCode='" + tcd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        }

        public bool UpdateTrans(entyTrans entytrans)
        {
            try
            {
                Connect();

                cmd.CommandText = "update Trans set Pest_name='" + entytrans.PestCd + "',Host_name='" + entytrans.HostCd + "',State_name='" + entytrans.StateCd + "',Info='" + entytrans.TranInfo + "',PlantPart='" + entytrans.TranPart + "' where TCode='" + entytrans.TranCd + "'";

                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Close();
                return false;
            }
            Disconnect();

        } 

       //Login

        //public bool getLogin(string uid,string pwd)
        //{
           
        //    Connect();
        //    cmd.CommandType = CommandType.Text;
        //    cmd.CommandText = "select * from Pest where UserID='" + uid + "' and Password='" + pwd + "'";
        //    reader = cmd.ExecuteReader();
           
            
        //    while (reader.Read())
        //    {
        //        entypest = new entyPest();
        //        entypest.PBCode = Convert.ToString(reader["BAYER_CODE"]);
        //        entypest.PSName = Convert.ToString(reader["SNAME"]);
        //        entypest.PCName = Convert.ToString(reader["CNAME"]);
        //        entypest.PKngdm = Convert.ToString(reader["KINGDOM"]);
        //        entypest.PPhylum = Convert.ToString(reader["PHYLUM"]);
        //        entypest.PSubPhylum = Convert.ToString(reader["SUBPHYLUM"]);
        //        entypest.PClass = Convert.ToString(reader["CLASS"]);
        //        entypest.POrder = Convert.ToString(reader["ORDER"]);
        //        entypest.PFName = Convert.ToString(reader["FAMILY"]);
        //        //entypest.PCategory = Convert.ToString(reader["CATEGORY"]);
        //        entypest.Painfo = Convert.ToString(reader["ainfo"]);
        //        entypest.PEcoImp = Convert.ToString(reader["ECOIMP"]);

        //        alst.Add(entypest);
        //    }
        //    Disconnect();
        //    return alst;
        //}

    }
}
